package cn.edu.cqupt.cxcy.dto;


import java.sql.ResultSet;
import java.util.*;

import cn.edu.cqupt.cxcy.dao.InvestorDao;
import cn.edu.cqupt.cxcy.model.Investor;
import cn.edu.cqupt.cxcy.model.Project;
import cn.edu.cqupt.cxcy.model.Team;
import cn.edu.cqupt.cxcy.util.SqlHelper;

public class InvestorImpl implements InvestorDao{



	//用来返回各种状态下项目总数的统计
	@Override
	public int SumOfProject() {
		String sql="select count(*) as sum from project ";
		int count=0;
		try
		{
			ResultSet rs=SqlHelper.executeQuery(sql,null);
			while(rs.next())
				count=rs.getInt("sum");
			//System.out.println(count);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
		}
		return count;
	}

	@Override
	public Project WhileShowOfOneProject(int p_id) {
		String sql="select * from project where P_id = "+Integer.toString(p_id)+ "";
		Project a=new Project();
		try
		{
			ResultSet rs=SqlHelper.executeQuery(sql, null);
			rs.next();

			a.setP_id(rs.getInt("P_id"));
			a.setP_name(rs.getString("P_name"));
			a.setP_ctime(rs.getDate("P_ctime"));
			a.setP_explain(rs.getString("P_explain"));
			a.setP_progress(rs.getInt("P_progress"));
			a.setP_content(rs.getString("P_content"));
			a.setTe_id(rs.getInt("te_id"));
			a.setP_logo(rs.getString("P_logo"));
			a.setP_ltime(rs.getDate("P_ltime"));
			a.setP_view(rs.getInt("P_view"));
			a.setP_status(rs.getInt("P_status"));

		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
		}

		return a;
	}

	@Override
	public boolean InvestProject(int status,int p_id) {
		boolean flag=true;
		String sql="update project set p_status = "+Integer.toString(status)+" where p_id ="+Integer.toString(p_id)+"";
		try
		{
			SqlHelper.executeUpdate(sql, null);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
		}
		return flag;
	}
	//用来返回各种状态下项目的集合
	@Override
	public List<Project> FirstOpenOfProjectOfSelf() {
		// TODO Auto-generated method stub
		List<Project> list=new ArrayList<Project>();
		String sql="select * from project ";
		try
		{  
			ResultSet rs=SqlHelper.executeQuery(sql, null);
			while(rs.next())
			{
				Project a=new Project();
				a.setP_id(rs.getInt("P_id"));
				a.setP_name(rs.getString("P_name"));
				a.setP_ctime(rs.getDate("P_ctime"));
				a.setP_explain(rs.getString("P_explain"));
				a.setP_progress(rs.getInt("P_progress"));
				a.setP_content(rs.getString("P_content"));
				a.setTe_id(rs.getInt("te_id"));
				a.setP_logo(rs.getString("P_logo"));
				a.setP_ltime(rs.getDate("P_ltime"));
				a.setP_view(rs.getInt("P_view"));
				a.setP_status(rs.getInt("P_status"));
				a.setPt_id(rs.getInt("Pt_id"));
				list.add(a);

			}}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
		}
		return list;
	}


	public int returnProjectId(String projectid)
	{
		String sql="select pt_id from projectype where pt_name = ?";
		int all=0;
		try
		{
			ResultSet rs=SqlHelper.executeQuery(sql,null);
			while(rs.next())
				all=rs.getInt("pt_id");
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
		}


		return all;

	}

	@Override
	public List<Project> ProjectWaitingInvestor(int Project_type) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public List<Project> TypeOfAll(int pt_id) {

		List<Project> list=new ArrayList<Project>();
		String sql="select * from project where pt_id = "+Integer.toString(pt_id)+" ";
		try
		{  
			ResultSet rs=SqlHelper.executeQuery(sql, null);
			while(rs.next())
			{
				Project a=new Project();
				a.setP_id(rs.getInt("P_id"));
				a.setP_name(rs.getString("P_name"));
				a.setP_ctime(rs.getDate("P_ctime"));
				a.setP_explain(rs.getString("P_explain"));
				a.setP_progress(rs.getInt("P_progress"));
				a.setP_content(rs.getString("P_content"));
				a.setTe_id(rs.getInt("te_id"));
				a.setP_logo(rs.getString("P_logo"));
				a.setP_ltime(rs.getDate("P_ltime"));
				a.setP_view(rs.getInt("P_view"));
				a.setP_status(rs.getInt("P_status"));
				list.add(a);

			}}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
		}
		return list;
	}	


	public String returnTeamName(int te_id)
	{String team=null;
	String sql="select te_name from  team where te_id = "+te_id+"";
	try
	{  ResultSet rs=SqlHelper.executeQuery(sql, null);
	while(rs.next())
		team=rs.getString("te_name");
	}
	catch(Exception e)
	{e.printStackTrace();
	}
	finally
	{SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
	}
	return team;
	}

	@Override
	public int returnPt_id(String pt_name)
	{
		int pt_id=0;
		//System.out.println(pt_name);
		String sql="select pt_id  from projectype where pt_name = "+pt_name+" ";
		try
		{
			//System.out.println(sql);
			ResultSet rs=SqlHelper.executeQuery(sql, null);
			while(rs.next())
				pt_id=rs.getInt("pt_id");
			//System.out.println);
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		finally
		{
			SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
		}

		return pt_id;
	}

	public int ReturnSumofAlltype(int pt_id)
	{   int sum=0;
	String sql="select count(*) as sum from project where pt_id= "+Integer.toString(pt_id)+"";
	try
	{
		//System.out.println(sql);
		ResultSet rs=SqlHelper.executeQuery(sql, null);
		while(rs.next())
			sum=rs.getInt("sum");
		//System.out.println);
	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	finally
	{
		SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
	}

	return sum;
	}
	public String ReturnproType (int pt_id)
	{   String type=null;
	String sql="select pt_name from projectype where pt_id ="+pt_id+"";
	try
	{
		//System.out.println(sql);
		ResultSet rs=SqlHelper.executeQuery(sql, null);
		while(rs.next())
			type=rs.getString("pt_name");
		//System.out.println);
	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	finally
	{
		SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
	}

	return type;
	}

	public Map<String,String> returnTeamname(int te_id)
	{int m_id=0;
	String sql="select  te_loname from team where te_id ="+te_id+"";
	Map<String,String> map=new HashMap<String,String>();
	try{
		ResultSet rs=SqlHelper.executeQuery(sql, null);
		while(rs.next())
			map.put("email", rs.getString("te_loname"));
		//System.out.println(map.get("email"));
		sql="select m_id from teamunite where te_id ="+te_id+" and tun_type ='leader'";
		try
		{
			rs=SqlHelper.executeQuery(sql, null);
			while(rs.next())
				m_id=rs.getInt("m_id");
			System.out.println(m_id);
			sql="select m_name,m_tel from member where m_id="+m_id+"";
			rs=SqlHelper.executeQuery(sql, null);
			while(rs.next())
			{ map.put("pro_pname", rs.getString("m_name"));
			map.put("pro_tel",rs.getString("m_tel"));}

		}
		catch(Exception e )
		{
			e.printStackTrace();
		}

	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	finally
	{
		SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
	}
	//System.out.println(map.get("pro_tel"));
	return map;
	}
	public List<Project> returnProjectByid(int inve_id,int status)
	{   List<Project> list=new ArrayList<Project>();
	int id[]=new int[100];
	String sql;
	if(status==1)
		sql="select p_id from projectinvest where i_id ="+inve_id+"";
	else 
		sql="select p_id from projectinvest where i_id <>"+inve_id+"";
	try
	{
		ResultSet rs=SqlHelper.executeQuery(sql, null);

		if(rs.next()!=false)
		{  id[0]=rs.getInt("p_id");
		for(int i=1;rs.next();i++)
		{
			id[i]=rs.getInt("p_id");

		}
		for(int i=0;id[i]!=0;i++)
		{
			Project one=this.WhileShowOfOneProject(id[i]);
			list.add(one);
		}
		}

	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	finally
	{
		SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
	}
	return list;
	}
	public int returnacount(int inve_id,int pro_id,int status)
	{int acount=0;
	String sql;
	if(status==1)
		sql="select pi_amount from projectinvest where i_id ="+inve_id+" and p_id="+pro_id+"";
	else
		sql="select pi_amount from projectinvest where p_id ="+pro_id+"";
	try
	{ResultSet rs=SqlHelper.executeQuery(sql, null);
	while(rs.next())
		acount=rs.getInt("pi_amount"); 
	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	finally
	{
		SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
	}
	return acount;
	}

	public Investor returnInvestor(int in_id)
	{   Investor one=new Investor();
	String sql="select * from Investor where i_id= "+in_id+"";
	try
	{ 
		ResultSet rs=SqlHelper.executeQuery(sql, null);
		while(rs.next())
		{	one.setI_ctime(rs.getDate("i_ctime"));
		one.setI_explain(rs.getString("i_explain"));
		one.setI_name(rs.getString("i_name"));
		one.setI_id(rs.getInt("i_id"));
		one.setI_loname(rs.getString("i_loname"));
		one.setI_tel(rs.getString("i_tel"));}

	}
	catch(Exception e)
	{
		e.printStackTrace();
	}
	finally
	{SqlHelper.close(SqlHelper.getRs(), SqlHelper.getPs(), SqlHelper.getConn());
	}

	return one;	}
}
